package dao.impl;

import dao.WordDao;
import util.JDBCUtils;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;
import java.util.TreeMap;


public class WordDaoImpl implements WordDao {
    @Override
    public Map<String, Integer> getWordCollect(int bid) {
        Connection connection =null;
        Statement statement =null;
        Map<String,Integer> map = new TreeMap<>();
        try {
            connection = JDBCUtils.getConnection();
            String sql ="SELECT word,frequency FROM word WHERE bid="+bid;
            statement= connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                map.put(resultSet.getString("word"),resultSet.getInt("frequency"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
        return map;
    }

    @Override
    public Map<String, Date> getExecuteWord(int uid) {
        return getWord(uid,"execute");
    }

    @Override
    public Map<String, Date> getKnowWord(int uid) {
        return getWord(uid,"know");
    }

    @Override
    public Map<String, Date> getFamiliarWord(int uid) {
        return getWord(uid,"familiar");
    }

    @Override
    public Map<String, Date> getremoveWord(int uid) {
        return getWord(uid,"remove");
    }

    @Override
    public int getRemoveWordNum(int uid) {
        return getWordNum(uid,"remove");
    }

    @Override
    public int getExecuteWordNum(int uid) {
        return getWordNum(uid,"execute");
    }

    @Override
    public int getKnowWordNum(int uid) {
        return getWordNum(uid,"know");
    }

    @Override
    public int getFamiliarWordNum(int uid) {
        return getWordNum(uid,"familiar");
    }

    @Override
    public Map<String, Integer> getBookWord(int bid) {
        Connection connection=null;
        Statement statement=null;
        Map<String, Integer> wordMap = new HashMap<>();
        try {
            connection = JDBCUtils.getConnection();
            statement=  connection.createStatement();
            String sql ="SELECT word,frequency FROM word WHERE bid="+bid;
            ResultSet resultSet = statement.executeQuery(sql);
            while(resultSet.next()){
                wordMap.put(resultSet.getString("word"),resultSet.getInt("frequency"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
        return wordMap;
    }

    private int getWordNum(int uid,String type){
        Connection connection=null;
        Statement statement=null;
        try {
            connection = JDBCUtils.getConnection();
            statement=  connection.createStatement();
            String sql ="SELECT count(*) FROM "+type+" WHERE uid="+uid;
            ResultSet resultSet = statement.executeQuery(sql);
            if (resultSet.next()){
                return resultSet.getInt("COUNT(*)");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
        return 0;
    }

    private Map<String, Date> getWord(int uid,String type){
        Connection connection =null;
        Statement statement =null;
        Map<String,Date> map = new TreeMap<>();
        try {
            connection = JDBCUtils.getConnection();
            String sql ="SELECT word,review_time FROM "+type+" WHERE uid="+uid;
            statement= connection.createStatement();
            ResultSet resultSet = statement.executeQuery(sql);
            while (resultSet.next()){
                map.put(resultSet.getString("word"),resultSet.getDate("review_time"));
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCUtils.close(statement,connection);
        }
        return map;
    }

}